Skip Headers

Oracle® OLAP Reference 10g Release 2 (10.2) Part Number B14350-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# OLAP_CONDITION Examples

Several sample queries using `OLAP_CONDITION` are shown in Example 29-2. These examples use the `PRICE_CUBE` in the `GLOBAL` analytic workspace. The cube has a time dimension, a product dimension, and measures for unit cost and unit price.

See Also: "OLAP_CONDITION Syntax" for complete descriptions of the syntax used in these examples.

The examples are based on a view called `unit_cost_price_view`. The SQL for creating this view is shown in Example 29-1. For information about creating views of analytic workspaces, see "OLAP_TABLE Overview".

Example 29-1 View of PRICE_CUBE in GLOBAL Analytic Workspace

    -- Create the logical row
    SQL>CREATE TYPE unit_cost_price_row AS OBJECT (
                aw_unit_cost          NUMBER,
                aw_unit_price         NUMBER,
                aw_product            VARCHAR2(50),
                aw_product_gid        NUMBER(10),
                aw_time               VARCHAR2(20),
                aw_time_gid           NUMBER(10),
                r2c                   RAW(32));

    -- Create the logical table
    SQL>CREATE TYPE unit_cost_price_table AS TABLE OF unit_cost_price_row;

    -- Create the view
    SQL>CREATE OR REPLACE VIEW unit_cost_price_view AS
        SELECT aw_unit_cost, aw_unit_price, aw_product, aw_product_gid,
               aw_time, aw_time_gid, r2c
          FROM TABLE(OLAP_TABLE(
             'global DURATION SESSION',
             'unit_cost_price_table',
             '',
             'MEASURE aw_unit_cost  FROM price_cube_unit_cost
              MEASURE aw_unit_price FROM price_cube_unit_price
              DIMENSION product WITH
                 HIERARCHY product_parentrel
                    INHIERARCHY product_inhier
                    GID aw_product_gid FROM product_gid
                 ATTRIBUTE aw_product FROM product_short_description
              DIMENSION time WITH
                 HIERARCHY time_parentrel
                    INHIERARCHY time_inhier
                    GID aw_time_gid FROM time_gid
                 ATTRIBUTE aw_time FROM time_short_description
              ROW2CELL r2c'));

    -- query the view
    SQL>SELECT * FROM unit_cost_price_view
                 WHERE aw_product = 'Hardware'
                 AND aw_time in ('2000', '2001', '2002', '2003')
                 ORDER BY aw_time;

    AW_UNIT_COST AW_UNIT_PRICE AW_PRODUCT AW_PRODUCT_GID AW_TIME AW_TIME_GID R2C
    ------------ ------------- ---------- -------------- ------- ----------- -----
       211680.12    224713.71  Hardware                3 2000             3  00...
       195591.60    207513.16  Hardware                3 2001             3  00...
       184413.05    194773.78  Hardware                3 2002             3  00...
        73457.31     77275.06  Hardware                3 2003             3  00...

Example 29-2 Queries of UNIT_COST_PRICE_VIEW Using OLAP_CONDITION

The queries in this example use `OLAP_CONDITION` to modify the query of `UNIT_COST_PRICE_VIEW` in Example 29-1. In each query, `OLAP_CONDITION` uses a different entry point to limit the `TIME` dimension to the year 2000.

In the first query, `OLAP_CONDIITON` uses entry point 0. The limited data is returned by `OLAP_TABLE`, and the limit remains in effect in the analytic workspace.

    SQL>SELECT * FROM unit_cost_price_view
                 WHERE aw_product = 'Hardware'
                 AND aw_time in ('2000', '2001', '2002', '2003')
                 AND OLAP_CONDITION(r2c,
                           'limit time to time_short_description eq ''2000''', 0)=1
                 ORDER BY aw_time;

    AW_UNIT_COST AW_UNIT_PRICE AW_PRODUCT AW_PRODUCT_GID AW_TIME AW_TIME_GID R2C
    ------------ ------------- ---------- -------------- ------- ----------- -----
       211680.12    224713.71  Hardware                3 2000             3  00...

    --Check status in the analytic workspace
    SQL>exec dbms_aw.execute('rpr time_short_description');

    TIME     TIME_SHORT_DESCRIPTION
    ----     ----------------------
      3      2000

    -- Reset status
    SQL>exec dbms_aw.execute('allstat');

In the next query, `OLAP_CONDIITON` uses entry point 1. The limited data is returned by `OLAP_TABLE`, but the limit does not remain in effect in the analytic workspace.

Note that the third parameter is not required in this case, since entry point 1 is the default.

    SQL>SELECT * FROM unit_cost_price_view
                 WHERE aw_product = 'Hardware'
                 AND aw_time in ('2000', '2001', '2002', '2003')
                 AND OLAP_CONDITION(r2c,
                           'limit time to time_short_description eq ''2000''', 1)=1
                 ORDER BY aw_time;

    AW_UNIT_COST AW_UNIT_PRICE AW_PRODUCT AW_PRODUCT_GID AW_TIME AW_TIME_GID R2C
    ------------ ------------- ---------- -------------- ------- ----------- -----
       211680.12    224713.71  Hardware                3 2000             3  00...

    --Check status in the analytic workspace
    SQL>exec dbms_aw.execute('rpr time_short_description');

    TIME     TIME_SHORT_DESCRIPTION
    ----     ----------------------
     19      Jan-98
     20      Feb-98
     21      Mar-98
     22      Apr-98
    .
    .
    .
      1      1998
      2      1999
      3      2000
      4      2001
     85      2002
    102      2003
    119      2004

    -- Reset status
    SQL>exec dbms_aw.execute('allstat');

In the final query, `OLAP_CONDIITON` uses entry point 2. The limit does not affect the data returned by `OLAP_TABLE`, but the limit remains in effect in the analytic workspace.

    SQL>SELECT * FROM unit_cost_price_view
                 WHERE aw_product = 'Hardware'
                 AND aw_time in ('2000', '2001', '2002', '2003')
                 AND OLAP_CONDITION(r2c,
                           'limit time to time_short_description eq ''2000''', 2)=1
                 ORDER BY aw_time;

    AW_UNIT_COST AW_UNIT_PRICE AW_PRODUCT AW_PRODUCT_GID AW_TIME AW_TIME_GID R2C
    ------------ ------------- ---------- -------------- ------- ----------- -----
       211680.12    224713.71  Hardware                3 2000             3  00...
       195591.60    207513.16  Hardware                3 2001             3  00...
       184413.05    194773.78  Hardware                3 2002             3  00...
        73457.31     77275.06  Hardware                3 2003             3  00...

    --Check status in the analytic workspace
    SQL>exec dbms_aw.execute('rpr time_short_description');

    TIME     TIME_SHORT_DESCRIPTION
    ----     ----------------------
      3      2000

# OLAP_CONDITION Syntax

The `OLAP_CONDITION` function executes an OLAP DML command at one of three entry points in the limit map used in a call to `OLAP_TABLE`.

Syntax

    OLAP_CONDITION(
              r2c          IN   RAW(32),
              expression   IN   VARCHAR2,
              event        IN   NUMBER DEFAULT 1);
          RETURN NUMBER;

Parameters

Table 29-1 OLAP_CONDITION Function Parameters

Parameter | Description
--- | ---
`r2c` | The name of a column specified by a `ROW2CELL` clause in the limit map. This parameter is used by `OLAP_CONDITION` to identify a particular invocation of `OLAP_TABLE`. The `ROW2CELL` column is used in the processing of the single-row functions. (See Chapter 30, "OLAP_EXPRESSION") `OLAP_CONDITION` simply uses it as an identifier. For information on creating a `ROW2CELL` column, see "Limit Map Parameter".
`expression` | A single OLAP DML command to be executed within the context of the `OLAP_TABLE` function identified by the r2c parameter. For information on the OLAP DML, see the Oracle OLAP DML Reference.
`event` | The event during `OLAP_TABLE` processing that will trigger the execution of the OLAP DML command specified by the expression parameter. This parameter can have the value 0, 1, or 2, as described in Table 29-2

Returns

The number `1` to indicate a successful invocation of `OLAP_CONDITION`.

Note

The entry points for `OLAP_CONDITION` are described in Table 29-2. Refer to "Order of Processing in OLAP_TABLE" to determine where each entry point occurs.

Table 29-2 Entry Points for OLAP_CONDITION in the OLAP_TABLE Limit Map

Entry Point | Description
--- | ---
0 | Execute the OLAP DML command after the `PREDMLCMD` clause of the limit map is processed and before the status of the dimensions in the limit map is saved. The entry point is between steps 1 and 2 in "Order of Processing in OLAP_TABLE". If `OLAP_CONDITION` limits any of the dimensions in the limit map, the limits remain in the workspace after the execution of `OLAP_TABLE` (unless a command in the `POSTDMLCMD` clause of the limit map changes the status).
1 | Execute the OLAP DML command after the conditions of the `WHERE` clause are satisfied and before the data is fetched. (Default.) The entry point is between steps 4 and 5 in "Order of Processing in OLAP_TABLE". If an OLAP DML command (other than `FETCH`) is specified in the olap_command parameter of `OLAP_TABLE`, it is executed after `OLAP_CONDITION` and before the data is fetched. (The use of a `FETCH` command in the olap_command parameter, or in `OLAP_CONDITION` itself, is not generally recommended. See "Using FETCH in the olap_command Parameter".) If `OLAP_CONDITION` limits any of the dimensions in the limit map, the limits remain in effect for the duration of the query only.
2 | Execute the OLAP DML command after the data is fetched and the status of dimensions in the limit map has been restored. The entry point is after step 8 in "Order of Processing in OLAP_TABLE". If `OLAP_CONDITION` limits any dimensions, the limits remain in the analytic workspace after the query completes.

Example

See "OLAP_CONDITION Examples".